using System.Linq;
using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 销售订单综合查询（支持条件与无条件）
    /// </summary>
    public class GetSalesOrderSummaryHandler : IRequestHandler<GetSalesOrderSummaryCommand, APIResult<GetSalesOrderSummaryDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetSalesOrderSummaryHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetSalesOrderSummaryDto>> Handle(GetSalesOrderSummaryCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var conn = _dbConnectionFactory.CreateConnection();

                var where = new List<string>();
                var p = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.SalesOrderNumber))
                {
                    where.Add("so.OrderCode LIKE @SalesOrderNumber");
                    p.Add("@SalesOrderNumber", $"%{request.SalesOrderNumber}%");
                }
                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    where.Add("so.CustomerName LIKE @CustomerName");
                    p.Add("@CustomerName", $"%{request.CustomerName}%");
                }
                if (!string.IsNullOrWhiteSpace(request.SalesPerson))
                {
                    where.Add("so.SalesPerson LIKE @SalesPerson");
                    p.Add("@SalesPerson", $"%{request.SalesPerson}%");
                }
                if (!string.IsNullOrWhiteSpace(request.SalesType))
                {
                    // 等值或模糊均可，默认模糊
                    where.Add("so.SalesChannel LIKE @SalesType");
                    p.Add("@SalesType", $"%{request.SalesType}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    where.Add("m.MaterName LIKE @MaterialName");
                    p.Add("@MaterialName", $"%{request.MaterialName}%");
                }

                var whereClause = where.Count > 0 ? ("WHERE " + string.Join(" AND ", where)) : string.Empty;

                var countSql = $@"
SELECT COUNT(*)
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId
INNER JOIN Mater m ON sod.MaterId = m.Id
{whereClause}";

                var total = await conn.ExecuteScalarAsync<int>(countSql, p);

                var offset = (request.PageIndex - 1) * request.PageSize;
                p.Add("@Offset", offset);
                p.Add("@PageSize", request.PageSize);

                var dataSql = $@"
SELECT 
  (@rn := @rn + 1) AS SerialNumber,
  so.OrderCode            AS SalesOrderNumber,
  so.CustomerName         AS CustomerName,
  so.SalesPerson          AS SalesPerson,
  IFNULL(so.SalesPersonId, '') AS SalesPersonId,
  so.SalesChannel         AS SalesType,
  m.MaterName             AS MaterialName,
  m.MaterCode             AS MaterialCode,
  m.ModelSpec             AS ModelSpecification,
  m.Unit                  AS MainUnit,
  DATE_FORMAT(so.OrderDate, '%Y-%m-%d')    AS OrderDate,
  DATE_FORMAT(so.DeliveryDate, '%Y-%m-%d') AS DeliveryDate,
  IFNULL(so.ContractNo,'')                 AS ContractNo
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId
INNER JOIN Mater m ON sod.MaterId = m.Id
CROSS JOIN (SELECT @rn := @Start) t
{whereClause}
ORDER BY so.OrderCode, m.MaterCode
LIMIT @PageSize OFFSET @Offset";

                p.Add("@Start", offset);

                var items = await conn.QueryAsync<SalesOrderSummaryItemDto>(dataSql, p);

                var dto = new GetSalesOrderSummaryDto
                {
                    TotalCount = total,
                    Items = items.ToList()
                };

                return new APIResult<GetSalesOrderSummaryDto>
                {
                    Code = APIEnums.Success,
                    Msg = "查询成功",
                    Data = dto
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetSalesOrderSummaryDto>
                {
                    Code = APIEnums.Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
